USE [NANS21V]
GO
/****** オブジェクト:  StoredProcedure [dbo].[ResultTrackList]    スクリプト日付: 01/23/2026 16:47:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultTrackList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultTrackList]
GO

CREATE PROCEDURE [dbo].[ResultTrackList]
	@TAIKAINO int,
	@KYOGINO int,
	@ROUNDNO int,
	@SUBNO int,
	@KUMINO int,
	@KYOGISYAFLG int,
	@SYUMOKUKUBUN int,
	@SUBSYUMOKUKUBUN int,
	@SUBKIROKUKUBUN int,	
	@SORTFLG  int
AS
select*from(
	SELECT DISTINCT LA.LANE, 
			KS.KYOGISYANO, 
			(KS.NUMBER1 + CONVERT(varchar, KS.NUMBER2)) AS NUMBER, 
			KS.SEINEN, 
			KS.GAKUNEN, 
			(CONVERT(varchar, KS.KYOGISYAKANA) + '<br/>' + CONVERT(varchar, KS.KYOGISYAMEI)) AS KYOGISYAMEI, 
			(CONVERT(varchar, S.SYOZOKUMEI) + '<br/>' + CONVERT(varchar, KS.KOJINSYOZOKUTIMEI)) AS SYOZOKUMEI,
			ST.SYOZOKUTIMEI,

			dbo.Kaze(@SYUMOKUKUBUN, @SUBSYUMOKUKUBUN, CASE WHEN RE.KIROKU = 0 THEN '' ELSE 
				CASE WHEN @SYUMOKUKUBUN=8 THEN
					dbo.Kiroku(RE.KIROKU,@SUBSYUMOKUKUBUN,@SUBKIROKUKUBUN
						,(CASE WHEN ISNUMERIC(RE.EXTEND2)  = 1 then RE.EXTEND2 else 3 END))
				else	
					dbo.Kiroku(RE.KIROKU,@SYUMOKUKUBUN,SYUMO.KIROKUKUBUN
						,(CASE WHEN ISNUMERIC(RE.EXTEND2) = 1 then RE.EXTEND2 else 3 END)) 
				end
		   	END, RE.EXTEND1)AS KIROKU,
			dbo.Kaze(@SYUMOKUKUBUN, @SUBSYUMOKUKUBUN, 
				CASE WHEN RE.KIROKU = 0 THEN '' ELSE dbo.Kiroku(RE.KIROKU,@SUBSYUMOKUKUBUN,@SUBKIROKUKUBUN,3) END, RE.EXTEND1
			)AS KIROKU_ORIGINAL,-- 桁調整なしの値

			(dbo.Kaze(@SYUMOKUKUBUN, @SUBSYUMOKUKUBUN, 'w', RE.EXTEND1) +' '+ dbo.Result(@SYUMOKUKUBUN,TKU.KYOGIFLG, TKU.KINOUFLG, TKU.HOST))AS KAZE,
			dbo.Jyuni(RE.JYUNI,SK.OPENFLG) AS JYUNI,
			(CASE WHEN STR(RE.JYUNI) = 0 THEN '' ELSE STR(RE.JYUNI) END) AS Sort,
			RE.EXTEND3, 
			(dbo.CommentCodeConvert(RE.RCOMMENT,LA.SYUKKETU)) AS RCOMMENT,
			RE.EXTEND1, 
			LA.SYUKKETU, 
			SK.OPENFLG, 
			SK.KIROKU as PREKIROKU,-- 202601 追加 資格記録
			RE.EXTEND2, 
			RE.EXTEND4, 
			dbo.Passage(RE.PLUS) AS PLUS,
			S.SYOZOKUNO, 
			ST.SYOZOKUTINO, 
			RE.EXTEND5,
			TKU.KYOGIFLG,
			TKU.KINOUFLG,
			TKU.HOST AS KHOST, 
			TKU.ROLLCALLFLG,
			TR.KUMIWAKEFLG,  
			TR.HOST AS RHOST,
			KSE.EXTEND7 AS KSE_EXTEND7, --20221006
			KSE.EXTEND8 AS KSE_EXTEND8, --202601 YCコメント
			CONVERT(varchar, KONSEI.KIROKU) AS KPREKIROKU--混成競技用の資格記録
	FROM TBL_KUMI AS TKU, 
		TBL_ROUND AS TR, 
		TBL_LANE AS LA 
		LEFT OUTER JOIN (
		  SELECT LA.LANE, 
				 RE.KIROKU, 
				 RE.EXTEND1, 
				 RE.JYUNI, 
				 RE.EXTEND3, 
				 RE.RCOMMENT, 
				 RE.PLUS, 
				 RE.RANK, 
				 RE.SANKOKIROKU, 
				 RE.EXTEND2, 
				 RE.EXTEND4, 
				 RE.EXTEND5 
			FROM TBL_LANE AS LA, 
				 TBL_RESULT AS RE 
			WHERE LA.TAIKAINO = RE.TAIKAINO 
			  AND LA.KYOGINO = RE.KYOGINO 
			  AND LA.SUBNO = RE.SUBNO 
			  AND LA.ROUNDNO = RE.ROUNDNO 
			  AND LA.KUMINO = RE.KUMINO 
			  AND LA.LANE = RE.LANE 
			  AND LA.TAIKAINO = @TAIKAINO 
			  AND LA.KYOGINO = @KYOGINO 
			  AND LA.ROUNDNO = @ROUNDNO 
			  AND LA.SUBNO = @SUBNO 
			  AND LA.KUMINO = @KUMINO
		) AS RE ON LA.LANE = RE.LANE 
		INNER JOIN TBL_KYOGISYA AS KS 
			ON LA.TAIKAINO = KS.TAIKAINO 
			AND LA.KYOGISYANO = KS.KYOGISYANO 
			LEFT JOIN TBL_KYOGISYA_EXTE AS KSE  --20221006 
				ON KS.TAIKAINO = KSE.TAIKAINO       --20221006 
				AND KS.KYOGISYANO = KSE.KYOGISYANO  --20221006 
		INNER JOIN TBL_KYOGI AS KYOGI --20140514
			ON LA.TAIKAINO = KYOGI.TAIKAINO 
			AND LA.KYOGINO = KYOGI.KYOGINO 
			INNER JOIN TBL_SYUMOKU_MASTER AS SYUMO --20140514
			ON KYOGI.SYUMOKUNO = SYUMO.SYUMOKUNO--20140514
		LEFT JOIN TBL_KONSEI_MASTER AS KM
			ON KM.SYUMOKUNO = KYOGI.SYUMOKUNO   
			AND KM.KONSEINO = @SUBNO
		LEFT JOIN TBL_KYOGISYA_KONSEI_UCHIWAKE_KIROKU as KONSEI 
			ON LA.TAIKAINO = KONSEI.TAIKAINO AND LA.KYOGINO = KONSEI.KYOGINO 
			AND KONSEI.SYUMOKUNO = KM.KONSEINO AND KONSEI.KYOGISYANO = KS.KYOGISYANO
		,TBL_SYOZOKU AS S 
		,TBL_SYOZOKUTI AS ST 
		,TBL_SANKAKYOGI AS SK 
			
	WHERE LA.TAIKAINO = SK.TAIKAINO 
		AND LA.KYOGISYANO = SK.KYOGISYANO 
		AND LA.KYOGINO = SK.KYOGINO 
		AND KS.TAIKAINO = S.TAIKAINO 
		AND KS.SYOZOKUNO1 = S.SYOZOKUNO 
		AND S.SYOZOKUTINO = ST.SYOZOKUTINO 
		AND LA.TAIKAINO = @TAIKAINO 
		AND LA.KYOGINO = @KYOGINO 
		AND LA.ROUNDNO = @ROUNDNO 
		AND LA.SUBNO = @SUBNO 
		AND LA.KUMINO = @KUMINO 
		AND KS.KYOGISYAFLG = @KYOGISYAFLG
		AND TKU.TAIKAINO = @TAIKAINO 
		AND TKU.KYOGINO = @KYOGINO 
		AND TKU.SUBNO = @SUBNO 
		AND TKU.ROUNDNO = @ROUNDNO
		AND TKU.KUMINO =LA.KUMINO
		AND TR.TAIKAINO = @TAIKAINO 
		AND TR.KYOGINO = @KYOGINO 
		AND TR.SUBNO = @SUBNO 
		AND TR.ROUNDNO = @ROUNDNO
		
) X
 ORDER BY 
	CASE
		 WHEN @SORTFLG =  1 THEN 
				CASE WHEN X.SYUKKETU = 2 THEN 1 ELSE 0 END
		 WHEN @SORTFLG =  1 THEN 
				CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END
	END ASC, 
	CASE
		 WHEN @SORTFLG =  1 THEN 
				CASE WHEN X.Sort IS NULL OR X.Sort = 0 THEN X.LANE
					 WHEN X.Sort IS NULL OR X.Sort = 0 THEN 1 END
	END ASC, 
	CASE
		 WHEN @SORTFLG =  1 THEN 
				X.Sort
	END ASC,
	X.LANE ASC
